|
Hi there. I'm going to show you how to use relative versus absolute referencing mode when you're recording a macro. So, I'm here in the Developer tab, I've got some data here, and I've just put some random numbers in. I've copied and pasted this onto a second sheet, just to make it easier when I start to make modifications I can easily copy and paste back here. So, let's go ahead and click into this cell. And right now, it's in absolute referencing mode, which means it's going to record the absolute references B5 and so on that I click, and not the relative references. So, let's go ahead and record a macro. So, I now start with B5 record a macro. So, I named this absolute. Now, what I'm going to do is the cell to the right. I'm going to make yellow, and the cell below it I'm going to make bold. And then what I'm going to do is I'm going to delete three columns over which is column E. I'm going to right-click on that column heading and do delete. Now, I'm going to return the cursor, the active cell back to B5. I'm going to go to Developer and I'm going to stop recording. Now, you notice, if I go to Visual Basic, and I open up the modules here, that we have the code there. So, this is what the macro recorder recorded, this is the code. You notice that a lot of these things are absolute references, right? Range C5 is an absolute reference B6. Column E is a absolute reference. So, let's go back to Excel, and I'm going to just go back to my second sheet here, I'm going to copy and am going to paste, and let's test our macro. So, let's put it back in B5, and let's go to our Macros and run the absolute macro. You see it did exactly what we had wanted to write it, deleted column E and formatted those cells like we wanted. Now, let's reset it once again. Now instead of starting in cell B5, I'm going to start maybe somewhere else before I run it. So, I'm going to do macro absolute. We're going to run, and it does the exact same thing. It didn't start in the active cell, it skipped back to those absolute references B5 and so on. Let's reset this once again, and we're going to record a macro in relative referencingmode. So, I'm going to start with the cursor again in the active cell in B5. I'm going to click on Use Relative References. Let's go ahead and record a macro. Let's just call this relative. And do the same thing where in the cell to the right, we're going to make yellow, the cell below, we're going to make bold. I'm going to right-click on column E and delete that, and then return the active cell to B5. And then we are going to stop recording. So, let's first look at the code. We go here and you see that it has a sub relative, and everything here you don't see in terms of absolute references. You do see absolute references with respect to here, but it's always this when you have a dot and then something. This is a sub object of an object. So, really, this is offsetting the active cell by three columns, and then it's column A, A of that active cell, which really corresponds to E in this case if we started in column B. So, let's go back to Excel and I'm going to reset control copy and I'm going to paste it here. And let's first make sure it works in B5. So, we run the relative. And it does the same thing. You see it deleted column E. And so, it's working fine. But now, if we start in a different active cell. So, maybe I start down here at negative four, and we run this, it does everything in terms of relative references. And you see instead of two, we have a six of the top there. Instead of deleting column E, because we started in column C, it deleted three columns over, one, two, three. All right? So, that's the difference between absolute and relative referencing. Now, let's do one more thing. I'm going to do a mixed referencing mode because you can change the referencing mode during a recording. So, let's go ahead and reset everything. I'm going to start in B5. Now, this works. From my experience, it doesn't really work that well going from absolute to relative during a recording, but if you can go relative to absolute. So, let's go ahead and start with B5. And I'm going to record a macro. Let's call this just mixed. And again, same thing. So, we're going to highlight to the right, bolden the cell below it. Now, I'm going to switch midway through the recording. I'm going to unclick that, and I'm going to right-click on. So, now, we're in absolute referencing. I'm going to right-click on E and delete that. And then, let's go back to our cell here and let's stop the recording. If we go back to our Visual Basic Editor, you see in our mixed macro. You see this active cell. This is a relative reference. But now, we switch to absolute and now we have an absolute references here, and then absolute reference there. So, instead of returning back to the original cell, it should return always to B5. It should always instead of deleting three columns over like we did with the relative, it's always going to delete column E. So, let's go ahead and see what happens when we run this. So, control copy and paste. And now, let's start here like we've been doing. Let's go ahead and run the mixed. So, this should do exactly what we've been doing. So, it deleted column E. But now, if we run this starting in a different cell. So for example, C7, the first part of our mixed macro was run in relative referencing. So, we should highlight D7, we should bolden C8. But then, we switched into absolute. So, we should instead of deleting three columns over one, two, three, so we deleted this column with our relative referencing when we started on C7. We're going to delete column E because it's an absolute reference. So, let's go to our Macros and we're going to run the mixed. And that's exactly what happens. And instead of this active cell returning to the original cell when I recorded, because I was still in absolute referencing, it goes back to B5, which is what I had when I recorded. All right. Now, you're going to have a quiz question. So you starting here and I'm going to record a macro. So, watch the screen clipping the video, and then I'm going to ask you, which cell is highlighted yellow if I ran the macro starting with cell B3, and which cell is going to be bold?
|